﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 标签或分类表操作类
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class TermsDAO
    {
        #region 增加标签
        /// <summary>
        /// 向标签表中插入一行数据, 返回受影响行数
        /// </summary>
        /// <param name="term">要插入的对象</param>
        /// <returns>返回受影响行数</returns>
        public static int Insert(Term term)
        {
            string sql = @"INSERT INTO [Blog_Terms]
                                    (
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_parent_id],
                                        [term_desc]
                                    )
                                   VALUES 
                                    (
                                        @Name,
                                        @Slug,
                                        @Type,
                                        @ParentId,
                                        @Description
                                    );";
            return ExecuteNonQuery(term, sql);
        }
        #endregion

        #region 删除标签

        #region 删除一条
        /// <summary>
        /// 根据id删除对应的标签记录, 返回受影响行数
        /// </summary>
        /// <param name="termid">要删除记录的ID</param>
        /// <returns>返回受影响行数</returns>
        public static int Delete(int termid)
        {
            string sql = "DELETE FROM [Blog_Terms] WHERE [term_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", termid));
        }
        #endregion

        #region 删除多条
        /// <summary>
        /// 根据id字符串删除对应的标签记录, 返回受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID字符串,以逗号分割"1,2,3...."</param>
        /// <returns>返回受影响行数</returns>
        public static int Delete(string ids)
        {
            string sql = "DELETE FROM [Blog_Terms] WHERE [term_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新标签
        /// <summary>
        /// 根据标签对象ID属性更新一个标签对象, 返回受影响行数
        /// </summary>
        /// <param name="term">要更新的对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Term term)
        {
            string sql = @"UPDATE 
                                        [Blog_Terms]
                                   SET
                                        [term_name]=@Name,
                                        [term_slug]=@Slug,
                                        [term_type]=@Type,
                                        [term_parent_id]=@ParentId,
                                        [term_desc]=@Description
                                   WHERE
                                        [term_id]=@Id;";
            return ExecuteNonQuery(term, sql);
        }
        #endregion

        #region 选取标签

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("term_id", "Blog_Terms");
        }
        #endregion

        #region 选取全部标签集合
        /// <summary>
        /// 取出当前标签表中的所有拓展元数据信息集合
        /// </summary>
        /// <returns>标签信息集合</returns>
        public static List<Term> SelectTerms()
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [Blog_Terms];";
            return Select(sql);
        }
        #endregion

        #region 根据ID选取标签对象
        /// <summary>
        /// 根据ID选取标签对象
        /// </summary>
        /// <param name="termid">标签ID</param>
        /// <returns>标签对象</returns>
        public static Term SelectTerm(int termid)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [Blog_Terms]
                                    WHERE
                                        [term_id]=@Id;";
            List<Term> list = Select(sql, new SqlParameter("@Id", termid));
            return list.Count > 0 ? list[0] : null;
        }
        #endregion

        #region 根据类型选取标签对象集合
        /// <summary>
        /// 根据类型选取标签对象集合
        /// </summary>
        /// <param name="termType">标签类型</param>
        /// <returns>标签对象</returns>
        public static List<Term> SelectTerms(string termType)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [Blog_Terms]
                                    WHERE
                                        [term_type]=@Type;";
            return Select(sql, new SqlParameter("@Type", termType.Trim().ToLower()));
        }
        #endregion

        #region 根据ID和类型选取标签对象
        /// <summary>
        /// 根据ID和类型选取标签对象
        /// </summary>
        /// <param name="termid">标签ID</param>
        /// <param name="termType">标签类型</param>
        /// <returns>标签对象</returns>
        public static Term SelectTerm(int termid, string termType)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [Blog_Terms]
                                    WHERE
                                        [term_id]=@Id
                                    AND
                                        [term_type]=@Type;";
            List<Term> list = Select(sql,
                 new SqlParameter("@Id", termid),
                 new SqlParameter("@Type", termType.Trim().ToLower()));
            return list.Count > 0 ? list[0] : null;
        }
        #endregion

        #region 根据类型选取标签总数
        /// <summary>
        /// 根据类型选取标签总数
        /// </summary>
        /// <returns>总数</returns>
        public static int SelectTermsCount(string termType)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                    COUNT
                                    (
                                        [term_id]
                                    )
                                   FROM
                                        [Blog_Terms]
                                   WHERE
                                        [term_type]=@Type;";
            return (int)SqlHelper.ExecuteScalar(sql, new SqlParameter("@Type", termType));
        }
        #endregion

        #endregion

        #region 私有操作方法

        #region select
        /// <summary>
        /// 私有选取数据方法
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Term> Select(string sql, params SqlParameter[] parameter)
        {
            List<Term> list = new List<Term>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Term term = new Term(
                        reader.GetInt32(0),
                        reader.GetString(1),
                        reader.GetString(2),
                        reader.GetString(3),
                        reader.GetInt32(4),
                        reader.IsDBNull(5) ? null : reader.GetString(5)
                        );
                    list.Add(term);
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有方法执行写入数据库操作
        /// </summary>
        /// <param name="term">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Term term, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Name", term.Name),
                new SqlParameter("@Slug", term.Slug.Trim().ToLower()),
                new SqlParameter("@Type", term.Type.Trim().ToLower()),
                new SqlParameter("@ParentId", term.ParentId),
                new SqlParameter("@Description", string.IsNullOrEmpty(term.Description) ? System.DBNull.Value : (object)term.Description),
                new SqlParameter("@Id", term.Id)
                );
        }
        #endregion

        #endregion
    }
}
